# -*- coding: utf-8 -*-
from openerp.osv import osv, fields
import datetime
import pytz
import random
from openerp.tools.translate import _
from openerp import models,SUPERUSER_ID
from openerp import tools

class check_in_sign_statistics_table(osv.osv):
    _name = "check.in.sign.statistics.table"
    _description = u'考勤签入统计表'
    _auto = False

    _columns = {
        'employee_id': fields.char(u'员工'),
        'create_date': fields.datetime(u'日期', required=False),
        'check_in_time': fields.char(u'签到时间', required=True),
        'time_difference': fields.char(u'时间差', help='此处时间差指的是上班时间和签入时间的时间差'),
    }

    # 建立视图
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'check_in_sign_statistics_table')
        cr.execute("""CREATE or REPLACE VIEW check_in_sign_statistics_table as (
            SELECT cis.id as id,
                   cis.check_in_time AS create_date,
                   ru.login AS employee_id,
                   to_char(cis.check_in_time AT TIME zone 'UTC', 'HH:MI:SS') AS check_in_time,
                  date_part('hour',((to_date(to_char(timezone('UTC'::text, cis.check_in_time), 'YYYY-MM-DD'::text), 'YYYY-MM-DD'::text)
                 + '08:30:00'::time without time zone)) - timezone('UTC'::text, cis.check_in_time)
                )* 60 + date_part('minute', ((to_date(to_char(timezone('UTC'::text, cis.check_in_time), 'YYYY-MM-DD'::text), 'YYYY-MM-DD'::text)
                 + '08:30:00'::time without time zone)) - timezone('UTC'::text, cis.check_in_time)
               ) AS time_difference
                   FROM check_in_sign cis INNER JOIN res_users ru ON ru.id=cis.check_in_uid
                    ORDER BY cis.check_in_time)
            """)


class check_out_sign_statistics_table(osv.osv):
    _name = "check.out.sign.statistics.table"
    _description = u'考勤签入统计表'
    _auto = False

    _columns = {
        'employee_id': fields.char(u'员工'),
        'create_date': fields.datetime(u'日期', required=False),
        'check_out_time': fields.char(u'签到时间', required=True),
        'time_difference': fields.char(u'时间差', help='此处时间差指的是下班时间和签出时间的差别'),
    }

    # 建立视图
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'check_out_sign_statistics_table')
        cr.execute("""CREATE or REPLACE VIEW check_out_sign_statistics_table as (
                  SELECT cis.id as id,
                        cis.check_out_time AS create_date,
                        ru.login AS employee_id,
                        to_char(cis.check_out_time AT TIME zone 'UTC', 'HH24:MI:SS') AS check_out_time,
                        date_part('hour',timezone('UTC'::text, cis.check_out_time) -
                        ((to_date(to_char(timezone('UTC'::text, cis.check_out_time), 'YYYY-MM-DD'::text), 'YYYY-MM-DD'::text)
                         + '17:30:00'::time without time zone)))* 60 + date_part('minute',timezone('UTC'::text, cis.check_out_time) -
                        ((to_date(to_char(timezone('UTC'::text, cis.check_out_time), 'YYYY-MM-DD'::text), 'YYYY-MM-DD'::text)
                         + '17:30:00'::time without time zone))) AS time_difference
                        FROM check_in_sign cis INNER JOIN res_users ru ON
                        ru.id=cis.check_in_uid WHERE  cis.is_check_out=TRUE
                         ORDER BY cis.check_out_time)
            """)